package com.lovefreedom.psi.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.lovefreedom.psi.bean.Category;
import com.lovefreedom.psi.bean.Goods;

public class Excel {

	/**
	 * 读取xlsx文件内容
	 * 
	 * @return List<Goods>对象
	 * @throws IOException
	 *             输入/输出(i/o)异常
	 */
	public List<Goods> readExcel() throws IOException {

		InputStream is = new FileInputStream("E:\\学习\\java\\货品导入模板.xls");
		System.out.println("读入文件");

		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		Goods Goods = null;
		List<Goods> list = new ArrayList<Goods>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < 1; numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 循环行Row
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				Goods = new Goods();
				// 循环列Cell
				// 0分类 1编号 2名称 3单位 4期初数量5参考进价6参考售价
				HSSFCell categoryName = hssfRow.getCell(0);
				if (categoryName == null) {
					continue;
				}
				Category category = new Category();
				category.setName(getValue(categoryName));
				category.setId(1);
				Goods.setCategory(category);

				HSSFCell code = hssfRow.getCell(1);
				if (code == null) {
					continue;
				}
				Goods.setCode(getValue(code));
				HSSFCell name = hssfRow.getCell(2);
				if (name == null) {
					continue;
				}
				Goods.setName(getValue(name));
				HSSFCell unit = hssfRow.getCell(3);
				if (unit == null) {
					continue;
				}
				Goods.setUnit(getValue(unit));

				HSSFCell beginInventory = hssfRow.getCell(4);
				if (beginInventory == null) {
					continue;
				}
				Goods.setBeginInventory(Float
						.parseFloat(getValue(beginInventory)));

				HSSFCell purchasePrice = hssfRow.getCell(5);
				if (purchasePrice == null) {
					continue;
				}
				Goods.setPurchasePrice(Float
						.parseFloat(getValue(purchasePrice)));
				HSSFCell sellingPrice = hssfRow.getCell(6);
				if (sellingPrice == null) {
					continue;
				}
				Goods.setSellingPrice(Float.parseFloat(getValue(sellingPrice)));

				list.add(Goods);

			}
			// 测试
			for (int i = 0; i < list.size(); i++) {
				Goods goods1 = list.get(i);
				System.out.println("name=" + goods1.getName());
			}
		}
		return list;
	}

	/**
	 * 得到Excel表中的值
	 * 
	 * @param hssfCell
	 *            Excel中的每一个格子
	 * @return Excel中每一个格子中的值
	 */
	@SuppressWarnings("static-access")
	private String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			// 返回数值类型的值
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

}
